package com.tagit;

import java.io.File;
import java.io.FilenameFilter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.Context;
import android.content.DialogInterface;
import android.content.SharedPreferences.Editor;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.widget.Toast;

/**
 * The PhotosDbAdapter class acts the main layer of abstraction between the
 * TagIt application and the underlying database.
 * 
 * The database contains three tables: a photos table, a tag table, and a
 * tagApplication table which creates a many to many relationship between tags
 * and photos. Photos are stored with a path in the database by keeping a path
 * to the photo in the photos table.
 * 
 * Every time a new PhotosDbAdapter object is created the Pictures folder will
 * be scanned for any new photos that may have been added.
 * 
 * The code here is heavily inspired by the NotesDbAdapter class from the
 * Notepad tutorial available at developers.andriod.google.com.
 * 
 * Alert dialog code based on example from
 * http://developer.android.com/resources
 * /samples/ApiDemos/src/com/example/android/apis/app/AlertDialogSamples.html
 * 
 * @author mischnal
 * 
 */
public class PhotosDbAdapter {

	public static final String KEY_ID = "_id";
	// Columns for Photos table
	public static final String KEY_URI = "uri";
	public static final String KEY_TITLE = "title";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_REJECTED = "rejected";
	public static final String TABLE_PHOTOS = "photos";

	// Columns for Tag table
	public static final String TABLE_TAGS = "tags";
	public static final String KEY_TAG_CONTENT = "tagContent";

	// Columns for AppliedTags
	public static final String KEY_PHOTO_ID = "photo_id";
	public static final String KEY_TAG_ID = "tag_id";
	public static final String KEY_TIME = "app_time";
	public static final String TABLE_APPLIED_TAGS = "tagApplications";

	private static final String TAG = "PhotosDbAdapter";

	private static final String DATABASE_NAME = "TagIt.db";
	private static final int DATABASE_VERSION = 2;

	private static final String PHOTO_TABLE_CREATE = "CREATE TABLE photos "
			+ "(_id integer primary key autoincrement, title text, description text,"
			+ "uri string not null, rejected boolean not null);";
	private static final String TAG_TABLE_CREATE = "CREATE TABLE tags"
			+ "(_id integer primary key autoincrement, tagContent text not null);";
	private static final String TAGAPPLICATIONS_TABLE_CREATE = "CREATE TABLE tagApplications"
			+ "(_id integer primary key autoincrement, photo_id integer, tag_id integer, appTime date,"
			+ " FOREIGN KEY (photo_id) REFERENCES photos(_id),  FOREIGN KEY (tag_id) REFERENCES tags(_id));";

	private final Context mCtx;
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	/**
	 * Inner class deals with the creation of database on file system.
	 * 
	 * @author mischnal
	 * 
	 */
	private class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(PHOTO_TABLE_CREATE);
			db.execSQL(TAG_TABLE_CREATE);
			db.execSQL(TAGAPPLICATIONS_TABLE_CREATE);
			Editor favTags = mCtx.getSharedPreferences(FavoriteTags.PREFS_NAME,
					Context.MODE_PRIVATE).edit();
			favTags.putBoolean("1F", true);
			favTags.putBoolean("2F", true);
			favTags.putString("1S", "Vacation");
			favTags.putString("2S", "People");
			favTags.putBoolean("1A", false);
			favTags.putBoolean("2A", false);
			favTags.putBoolean("1D", true);
			favTags.putBoolean("2D", true);
			favTags.commit();
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS notes");
			onCreate(db);
		}
	}

	public PhotosDbAdapter(Context ctx) {
		mCtx = ctx;
	}

	/**
	 * Open the database creating a new one if necessary. This call must be made
	 * before making any other calls on this object.
	 * 
	 * @return Return the PhotoDbAdapter object is was called with
	 * @throws SQLException
	 *             If new database cannot be created
	 */
	public PhotosDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		populateDummyDb();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	/**
	 * Add a photo to the database
	 * 
	 * @param title
	 *            The title of the photo
	 * @param description
	 *            A brief text description
	 * @param path
	 *            A path to the photo on the phones file system
	 * @param rejected
	 *            If the photo is rejected or not
	 * @return The row id created for the new photo
	 */
	public long addPhoto(String title, String description, String path) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TITLE, title);
		initialValues.put(KEY_DESCRIPTION, description);
		initialValues.put(KEY_URI, path);
		initialValues.put(KEY_REJECTED, false);

		return mDb.insert(TABLE_PHOTOS, null, initialValues);
	}

	/**
	 * Adds all of the photos from the Pictures folder that are not currently in
	 * the database
	 */
	private void populateDummyDb() {
		List<Long> ids = new ArrayList<Long>();
		File rootDir = Environment.getExternalStorageDirectory();
		File[] files = rootDir.listFiles(new FilenameFilter() {
			public boolean accept(File dir, String filename) {
				return filename.equals("Pictures");
			}
		});

		// Hits here if no SDCard
		if (files == null) {
			return;
		}

		// Hits here if no pictures in the Pictures folder on sdcard
		if (files.length == 0)
			return;

		File[] pictures = files[0].listFiles();
		for (int i = 0; i < pictures.length; i++) {
			String path = pictures[i].getAbsolutePath();
			Cursor c = mDb.query(TABLE_PHOTOS, new String[] { KEY_ID }, KEY_URI
					+ "= ?", new String[] { path }, null, null, null);
			// Checking for duplicates
			if (c.getCount() == 0) {
				ids.add(addPhoto(pictures[i].getName(), null, path));
			}
			c.close();
		}

		// Uncomment this if you would like dummy tags added to photos
		/*
		 * for (Long x : ids) { addTagToPhoto(x, "puppies"); addTagToPhoto(x,
		 * "kittens"); addTagToPhoto(x, "bunnies"); }
		 */

	}

	/**
	 * Adds a tag to a photo. Does not reject duplicates
	 * 
	 * @param photo_id
	 *            The row id of the photo
	 * @param tagContent
	 *            The text of the tag
	 * @return The row_id in the tagApplications table
	 */
	public long addTagToPhoto(long photo_id, String tagContent) {
		String[] selArg = { tagContent };
		Cursor tag = mDb.query(TABLE_TAGS, new String[] { KEY_ID },
				KEY_TAG_CONTENT + "= ?", selArg, null, null, null);
		long tag_id;
		if (tag.getCount() == 0) {
			ContentValues initialValue = new ContentValues();
			initialValue.put(KEY_TAG_CONTENT, tagContent);
			tag_id = mDb.insert(TABLE_TAGS, null, initialValue);
		} else {
			tag.moveToFirst();
			tag_id = tag.getLong(0);
		}

		tag.close();

		return addTagToPhoto(photo_id, tag_id);

	}

	/**
	 * Add a tag to a photo does not reject duplicates
	 * 
	 * @param photo_id
	 *            rowId of photos
	 * @param tag_id
	 *            rowId of tag
	 * @return rowId of newly created entry in tagApplications table
	 */
	public long addTagToPhoto(long photo_id, long tag_id) {
		Cursor cursor = mDb.query(TABLE_APPLIED_TAGS, new String[] { KEY_ID },
				"photo_id = ? AND tag_id = ?", new String[] { photo_id + "",
						tag_id + "" }, null, null, null);
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			long result = cursor.getLong(cursor.getColumnIndex(KEY_ID));
			cursor.close();
			return result;
		} else {
			ContentValues initialValue = new ContentValues();
			initialValue.put(KEY_PHOTO_ID, photo_id);
			initialValue.put(KEY_TAG_ID, tag_id);
			long row_id = mDb.insert(TABLE_APPLIED_TAGS, null, initialValue);
			mDb.execSQL("UPDATE tagApplications SET appTime=datetime('now') WHERE _id="
					+ row_id);
			return row_id;
		}
	}

	public boolean removePhoto(long row_id) {
		int r = mDb.delete(TABLE_PHOTOS, "_id = ?",
				new String[] { row_id + "" });
		return r > 0;
	}

	// Method for use in the view Tags class to show a confirmation message
	// before deleting tag
	public boolean removeTagMsg(final long tagApp_id, final View v,
			final PhotoCursorAdapter pCA) {
		final boolean r = true;
		AlertDialog.Builder builder = new AlertDialog.Builder(v.getContext());
		builder.setMessage("Are you sure you want to delete this tag?")
				.setCancelable(false)
				.setPositiveButton("Yes",
						new DialogInterface.OnClickListener() {
							public void onClick(DialogInterface dialog, int id) {
								removeTag(tagApp_id);
								ViewPhoto hax = (ViewPhoto) mCtx;
								hax.fill_tag_table();
							}
						})
				.setNegativeButton("No", new DialogInterface.OnClickListener() {
					public void onClick(DialogInterface dialog, int id) {
						dialog.cancel();
					}
				});
		AlertDialog alert = builder.create();
		alert.show();
		return r;
	}

	public boolean removeTag(long photo_id, String tag) {
		Cursor c = mDb
				.query(TABLE_TAGS, new String[] { KEY_ID }, KEY_TAG_CONTENT
						+ "= ?", new String[] { tag }, null, null, null);
		if (c.getCount() == 0) {
			return false;
		} else {
			c.moveToFirst();
			long id = c.getLong(c.getColumnIndex(KEY_ID));
			return removeTag(photo_id, id);
		}
	}

	public boolean removeTag(long photo_id, long tag_id) {
		Cursor c = mDb.query(TABLE_APPLIED_TAGS, new String[] { KEY_ID },
				KEY_PHOTO_ID + " = ? and " + KEY_TAG_ID + " = ?", new String[] {
						photo_id + "", tag_id + "" }, null, null, null);
		if (c.getCount() == 0) {
			return false;
		} else {
			c.moveToFirst();
			long id = c.getLong(c.getColumnIndex(KEY_ID));
			return removeTag(id);
		}
	}

	public boolean removeTag(long tagApp_id) {
		int r = mDb.delete(TABLE_APPLIED_TAGS, "_id = ?",
				new String[] { tagApp_id + "" });
		return r > 0;
	}

	/**
	 * Get a cursor to all of the tags applied to a photo
	 * 
	 * @param photo_id
	 *            The rowId of the photo in question
	 * @return A cursor containing the tagRowId and the tagContent for every tag
	 *         on the photos
	 */
	public Cursor getTags(long photo_id) {
		return mDb
				.rawQuery(
						"SELECT tagApplications._id, tagContent FROM tags JOIN tagApplications ON tags._id = tagApplications.tag_id WHERE "
								+ "photo_id = ? ORDER BY tagApplications.appTime DESC",
						new String[] { photo_id + "" });
	}

	/**
	 * Get a cursor to some photos. This cursor can be passed to
	 * PhotoCursorAdapter to display photos in a list.
	 * 
	 * @param num
	 *            The number of photos selected.
	 * @return A cursor containing all columns of the photos table.
	 */
	public Cursor selectPhotos(int num) {
		return mDb.query(TABLE_PHOTOS, null, "rejected=?",
				new String[] { "0" }, null, null, null, num + "");
	}

	public Cursor selectRejected(int num) {
		return mDb.query(TABLE_PHOTOS, null, "rejected=?",
				new String[] { "1" }, null, null, null, num + "");
	}

	public void rejectPhoto(long photo_id) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_REJECTED, true);
		mDb.update(TABLE_PHOTOS, cv, "_id=?",
				new String[] { Long.toString(photo_id) });
	}

	public void unrejectPhoto(long photo_id) {
		ContentValues cv = new ContentValues();
		cv.put(KEY_REJECTED, false);
		mDb.update(TABLE_PHOTOS, cv, "_id=?",
				new String[] { Long.toString(photo_id) });
	}

	public void rejectMultiplePhotos(Collection<Long> photo_id) {
		Long[] photoIdArrayLong = new Long[photo_id.size()];
		photo_id.toArray(photoIdArrayLong);
		ContentValues cv = new ContentValues();
		cv.put(KEY_REJECTED, true);

		for (Long id : photo_id) {
			// photoIdArrayString[i] = Long.toString(photoIdArrayLong[i]);
			// Log.v("photos in the array", photoIdArrayString[i]);
			mDb.update(TABLE_PHOTOS, cv, "_id=?",
					new String[] { Long.toString(id) });
		}

	}

	public void unrejectMultiplePhotos(Collection<Long> photo_id) {
		Long[] photoIdArrayLong = new Long[photo_id.size()];
		photo_id.toArray(photoIdArrayLong);
		ContentValues cv = new ContentValues();
		cv.put(KEY_REJECTED, false);

		for (Long id : photo_id) {
			// photoIdArrayString[i] = Long.toString(photoIdArrayLong[i]);
			// Log.v("photos in the array", photoIdArrayString[i]);
			mDb.update(TABLE_PHOTOS, cv, "_id=?",
					new String[] { Long.toString(id) });
		}

	}

	public List<String> getAllTags() {
		Cursor c = mDb.query(TABLE_TAGS, new String[] { KEY_TAG_CONTENT },
				null, null, null, null, KEY_TAG_CONTENT);
		List<String> result = new ArrayList<String>();
		if (!c.moveToFirst())
			return result;

		int columnIndex = c.getColumnIndex(KEY_TAG_CONTENT);
		do {
			result.add(c.getString(columnIndex));
		} while (c.moveToNext());
		return result;
	}

	public Cursor countTags(Collection<Long> photoIds) {
		StringBuilder selectClause = new StringBuilder();
		boolean firstPass = true;
		for (Long pid : photoIds) {
			if (firstPass) {
				selectClause.append("WHERE photo_id = " + pid);
				firstPass = false;
			} else {
				selectClause.append(" or photo_id = " + pid);
			}
		}

		if (firstPass) {
			return null;
		}

		// Sorry for the crazy query will write a real comment later
		Cursor counter = mDb
				.rawQuery(
						"SELECT tags._id, tags.tagContent, count(*) as tag_count from tagApplications "
								+ " join tags on tagApplications.tag_id = tags._id "
								+ selectClause.toString()
								+ " GROUP BY tags._id ORDER BY tagApplications.appTime DESC",
						null);
		return counter;
	}

}